<?php
/**
 * Created by PhpStorm.
 * User: a
 * Date: 2016/6/12
 * Time: 15:44
 */
namespace Admin\Model;
use Think\Model;

class BorrowinfoModel extends Model{
    protected $trueTableName = 'bypal_borrow_info';

    public function borrow_status(){
        $sql="select ls_id,ls_value from bypal_cust_loanstatus where 1=1";
        $Model = new \Think\Model();
        $values=$Model->query($sql);
        return $values;
    }

    /**
     * 查询客户最大分期金额，和分期订单数
     * @param $cust_id
     * @return mixed
     */
    public function report_borrow($cust_id){
        $sql="select count(borrow_id) count,max(borrow_money*1) max_money,sum(t2.isoverdue) sum_isoverdue from bypal_borrow_info t1
        left join (select sum(isoverdue) isoverdue,binfo_id from bypal_repay_detail group by binfo_id) t2 on t1.borrow_id=t2.binfo_id
        where borrow_custid=$cust_id and borrow_stateid=2";
        $Model = new \Think\Model();
        $values=$Model->query($sql);
        return $values;
    }

    public function borrow_by_custid($cust_id){
        $sql="select t1.trade_contractnumber borrow_id,t2.fac_simple_name,t3.`name` borrow_title,(t1.trade_allmoney-t1.fac_payment) borrow_money,t1.trade_rate borrow_rate
        ,case when t1.delay_time=0 then t1.fac_period else CONCAT(t1.fac_period,'+',t1.delay_time) end loan_period,
        0 borrow_status,'申请中' borrow_statusname from bypal_facilitator_trade t1 left join bypal_facilitator_info t2 on t1.fid=t2.id
        left join bypal_facilitator_itype t3 on t2.fac_separatetype=t3.trade_number where t1.tid=1 and t1.cid='$cust_id'
        union ALL
        select t1.borrow_id,t3.fac_simple_name,t1.borrow_title,t1.borrow_money,t1.borrow_rate,case when t2.delay_time=0 then t1.borrow_loanperiod
        else CONCAT(t1.borrow_loanperiod,'+',t2.delay_time) end loan_period,t1.borrow_stateid,t5.ls_value borrow_statusname from bypal_borrow_info t1
        left join bypal_facilitator_trade t2 on t1.borrow_id=t2.trade_contractnumber
        left join bypal_facilitator_info t3 on t1.borrow_busid=t3.id
        left join bypal_borrow_buinessinfo t4 on t1.borrow_id=t4.binfo_borrowid
				left join bypal_cust_loanstatus t5 on t1.borrow_stateid=t5.ls_id where t1.borrow_custid='$cust_id'";
        $Model = new \Think\Model();
        $values=$Model->query($sql);
        return $values;
    }
    public function borrow_apply_count($search_array){
        $sql="select count(t1.borrow_id) count
        from bypal_borrow_info t1 left join bypal_cust_info t2 on t1.borrow_custid=t2.cust_id
        left join bypal_facilitator_info t4 on t1.borrow_busid=t4.id
        where 1=1 and borrow_stateid not in (2,4)";
        if($search_array['select_custName']!=''){
            $sql.=" and t2.cust_name like '%".$search_array['select_custName']."%'";
        }
        if($search_array['select_businessName']!=''){
            $sql.=" and t4.fac_simple_name like '%".$search_array['select_businessName']."%'";
        }
        if($search_array['select_borrowNum']!=''){
            $sql.=" and t1.borrow_id ='".$search_array['select_borrowNum']."'";
        }
        if($search_array['select_checkState']!=''){
            $sql.=" and t1.borrow_stateid =".$search_array['select_checkState'];
        }
        $Model = new \Think\Model();
        $values=$Model->query($sql);
        return $values[0]["count"];
    }

    public function borrow_apply($search_array,$page,$rows){
        $sql="select t1.borrow_id,t2.cust_name,t1.borrow_money,t5.name,t4.fac_simple_name,t1.borrow_rate,t7.ls_value,t1.borrow_custid,
        t3.delay_time,t8.supple_count
        from bypal_borrow_info t1 left join bypal_cust_info t2 on t1.borrow_custid=t2.cust_id
        left join bypal_facilitator_trade t3 on t1.borrow_id=t3.trade_contractnumber
        left join bypal_facilitator_info t4 on t1.borrow_busid=t4.id
        left join bypal_facilitator_itype t5 on t4.fac_separatetype=t5.trade_number
        left join bypal_cust_loanstatus t7 on t1.borrow_stateid=t7.ls_id
        left join (select count(supple_id) supple_count,borrow_id from bypal_borrow_suppledetail group by borrow_id) t8 on t1.borrow_id=t8.borrow_id
        where 1=1 and borrow_stateid not in (2,4)";

        if($search_array['select_custName']!=''){
            $sql.=" and t2.cust_name like '%".$search_array['select_custName']."%'";
        }
        if($search_array['select_businessName']!=''){
            $sql.=" and t4.fac_simple_name like '%".$search_array['select_businessName']."%'";
        }
        if($search_array['select_borrowNum']!=''){
            $sql.=" and t1.borrow_id ='".$search_array['select_borrowNum']."'";
        }
        if($search_array['select_checkState']!=''){
            $sql.=" and t1.borrow_stateid =".$search_array['select_checkState'];
        }
        if($search_array['select_start_time']!=''&&$search_array['select_end_time']!=''){
            $start_time=strtotime($search_array['select_start_time']);
            $end_time=strtotime($search_array['select_end_time']." 23:59:59");
            $sql.=" and t1.borrow_createdate between '".$start_time."' and '".$end_time."'";
        }
        $sql.="order by t1.borrow_createdate desc";
        $maxPage=($page-1)*$rows;
        $limit=" limit $maxPage,$rows";
        $sql.=$limit;
        $Model = new \Think\Model();
        $values=$Model->query($sql);
        return $values;
    }

    public function select_borrow_cancel($search_array,$page,$rows){
        $sql="select t1.borrow_id,t3.cust_name,t2.fac_simple_name,t1.borrow_money,case when t1.borrow_unworktime=0 then '未设置' else FROM_UNIXTIME(t1.borrow_unworktime,'%Y-%m-%d') end borrow_unworktime
        ,t5.facp_name,t5.facp_phone,t5.facp_loginname
        from bypal_borrow_info t1 left join bypal_facilitator_info t2 on t1.borrow_busid=t2.id
        left join bypal_cust_info t3 on t1.borrow_custid=t3.cust_id
        left join bypal_facilitator_trade t4 on t1.borrow_id=t4.trade_contractnumber
        left join bypal_facilitator_person t5 on t4.trade_pernumber=t5.facp_fid where t1.borrow_stateid=4";

        $count_sql="select count(t1.borrow_id) count from bypal_borrow_info t1 left join bypal_facilitator_info t2 on t1.borrow_busid=t2.id
        left join bypal_cust_info t3 on t1.borrow_custid=t3.cust_id
        where t1.borrow_stateid=4 ";

        $where="";
        if($search_array['select_borrowNum']!=''){
            $where.=" and t1.borrow_id=".$search_array['select_borrowNum'];
        }
        if($search_array['select_custName']!=''){
            $where.=" and t3.cust_name like '%".$search_array['select_custName']."%'";
        }
        if($search_array['select_businessName']!=''){
            $where.=" and t2.fac_simple_name like '".$search_array['select_businessName']."'";
        }
        $maxPage=($page-1)*$rows;
        $limit=" limit $maxPage,$rows";
        $sql.=$where." order by t1.borrow_unworktime desc ".$limit;
        $count_sql.=$where;

        $Model = new \Think\Model();
        $values=$Model->query($sql);
        $values_count=$Model->query($count_sql);
        $result['total']=$values_count[0]['count'];
        $result['rows']=$values;
        return $result;

    }

    public function borrow_info_detail($borrow_id){
        $sql="select t1.borrow_id,t4.ls_value,t5.fac_name,t1.borrow_title,t1.borrow_busid,t1.borrow_money,t16.facp_name,t16.facp_phone,t16.facp_loginname,
                t7.fm_result,t10.count,t11.`name`,t1.borrow_rate,t5.fac_contact,t5.fac_contactPhone,t1.borrow_custid,t3.contract_annex,
                FROM_UNIXTIME(t1.borrow_createdate,'%Y-%m-%d') borrow_createdate,t2.cust_name,t3.fac_payment,t8.ref_count,
                FROM_UNIXTIME(t1.borrow_passtime,'%Y-%m-%d') borrow_passtime,t2.cust_phone,t1.borrow_extramoney,t1.borrow_loanperiod,t1.borrow_stateid,
                t3.delay_time,t5.fac_rate_method,t3.service_money,t17.zhima_score,t18.report_annex
                from bypal_borrow_info t1
                left join bypal_cust_info t2 on t1.borrow_custid=t2.cust_id
                left join bypal_facilitator_trade t3 on t1.borrow_id=t3.trade_contractnumber
                left join bypal_cust_loanstatus t4 on t1.borrow_stateid=t4.ls_id
                left join bypal_facilitator_info t5 on t1.borrow_busid=t5.id
                left join bypal_impl_fm t7 on t1.borrow_id=t7.code
                left join bypal_impl_countcredit t10 on t1.borrow_id=t10.applyId
                left join bypal_facilitator_itype t11 on t5.fac_separatetype=t11.trade_number
                left join bypal_facilitator_person t16 on t3.trade_pernumber=t16.facp_id
                left join (select count(ref_id) ref_count,ref_custid from bypal_cust_reference group by ref_custid) t8 on t1.borrow_custid=t8.ref_custid
                left join bypal_cust_creditscore t17 on t1.borrow_custid=t17.cust_id
                left join bypal_cust_report t18 on t1.borrow_custid=t18.cust_id
                where t1.borrow_id=$borrow_id";
        $Model = new \Think\Model();
        $values=$Model->query($sql);
        return $values[0];
    }

    public function borrow_supple_detail($borrow_id){
        $sql="select t1.id,t1.supple_information,t1.type,t2.id supple_detail_id,t2.supple_information supple_information_detail
        from bypal_borrow_suppleinformation t1
        left join (select id,supple_id,supple_information from bypal_borrow_suppledetail where borrow_id='$borrow_id') t2 on t1.id=t2.supple_id order by t1.type";

        $Model = new \Think\Model();
        $values=$Model->query($sql);
        return $values;
    }

    public function borrow_cust_info($borrow_id){
        $sql="select t1.borrow_money,t2.cust_name,t2.cust_identity,t2.cust_phone,t2.cust_id,t1.borrow_title from bypal_borrow_info t1 left join bypal_cust_info t2 on t1.borrow_custid=t2.cust_id
        where t1.borrow_id=$borrow_id";

        $Model = new \Think\Model();
        $values=$Model->query($sql);
        return $values[0];
    }

    public function update_borrow_first_examine($borrow_id){
        $nowtime=time();
        $sql = "update bypal_borrow_info SET borrow_stateid = 5 ,borrow_passtime = $nowtime where borrow_id = $borrow_id";
        $Model = new \Think\Model();
        $values=$Model->execute($sql);
        return $values;
    }

    public function update_borrow_second_examine($borrow_id){
        $sql="update bypal_borrow_info Set borrow_count =650,borrow_stateid=7 where borrow_id=$borrow_id";
        $Model = new \Think\Model();
        $values=$Model->execute($sql);
        return $values;
    }

    public function update_borrow_reject_examine($borrow_id,$state){
        $nowtime=time();
        $sql="update bypal_borrow_info Set borrow_unworktime ='$nowtime',borrow_stateid=$state where borrow_id=$borrow_id";
        $Model = new \Think\Model();
        $values=$Model->execute($sql);
        return $values;
    }

    public function select_borrow_period($borrow_id){
        $sql="select t1.dateslip,IFNULL(t2.state,2) state,t1.borrow_period from bypal_borrow_date t1
        left join (select borrow_period,state from bypal_borrow_period where borrow_id='$borrow_id') t2 on t1.borrow_period=t2.borrow_period
        order by t1.borrow_period ";
        $Model = new \Think\Model();
        $values=$Model->query($sql);
        return $values;
    }

    public function select_borrow_data($borrow_id){
        $sql="select t1.id,t1.data_name,IFNULL(t2.state,4) state,case when state=1 then 1 else 0 end checked from bypal_borrow_data t1
        left join (select data_id,state from bypal_borrow_dataorder where borrow_id='$borrow_id') t2 on t1.id=t2.data_id
        where t1.data_type!=5";
        $Model = new \Think\Model();
        $values=$Model->query($sql);
        return $values;
    }

    public function insert_borrow_period($borrow_id,$borrow_period){
        $sql="insert into bypal_borrow_period(borrow_id,borrow_period,state) values('$borrow_id','$borrow_period',1)";
        $Model = new \Think\Model();
        $values=$Model->execute($sql);
        return $values;
    }

    public function cancel_borrow_period($borrow_id){
        $sql="update bypal_borrow_period set state=0 where borrow_id='$borrow_id'";
        $Model = new \Think\Model();
        $values=$Model->execute($sql);
        return $values;
    }

    public function submit_borrow_period($borrow_id,$borrow_period){
        $sql="update bypal_borrow_period set state=1 where borrow_id='$borrow_id' and borrow_period='$borrow_period'";
        $Model = new \Think\Model();
        $values=$Model->execute($sql);
        return $values;
    }

    public function update_borrow_period($borrow_id,$borrow_period){
        $sql="update bypal_borrow_info set borrow_period='$borrow_period' where borrow_id='$borrow_id'";
        $Model = new \Think\Model();
        $values=$Model->execute($sql);
        return $values;
    }

    public function insert_borrow_data($borrow_id,$cust_id,$data_id){
        $sql="insert into bypal_borrow_dataorder(borrow_id,cust_id,data_id,state) values('$borrow_id','$cust_id','$data_id',1)";
        $Model = new \Think\Model();
        $values=$Model->execute($sql);
        return $values;
    }
    public function update_borrow_data($borrow_id,$data_id,$state){
        $sql="update bypal_borrow_dataorder set state='$state' where borrow_id='$borrow_id' and data_id='$data_id'";
        $Model = new \Think\Model();
        $values=$Model->execute($sql);
        return $values;
    }

    public function update_borrow_state_fordata($borrow_id){
        $sql="update bypal_borrow_info set borrow_stateid=8 where borrow_id='$borrow_id'";
        $Model = new \Think\Model();
        $values=$Model->execute($sql);
        return $values;
    }
}

